package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import orm.CollectivityTeachingPlanMonth;
import orm.CollectivityTeachingPlanWeek;
import util.DBHelper;

public class CTeachingPlanMonthDAO {
	
	
	public void insertCTPMonth(Statement statement, CollectivityTeachingPlanMonth ctpm) throws SQLException {
		statement.execute("insert into collectivityteachingplanmonth values('" + ctpm.getCMonthArrangeID() + "','" + ctpm.getClassID() + "','" +
				ctpm.getExecutionDate() + "', '" + ctpm.getEnactmentDate() + "', '" + ctpm.getPushDateStart() +
				"','" + ctpm.getPushDateEnd() + "', '" + ctpm.getMonthThemeName() + "', '" + ctpm.getConstitutor() + "', " + ctpm.getTeacherID() + ")");
	}
	
	public void updateCTPMonth(Statement statement, CollectivityTeachingPlanMonth ctpm) throws SQLException {
		statement.execute("update collectivityteachingplanmonth set ClassID = '" +
				ctpm.getClassID() + "', ExecutionDate = '" + ctpm.getExecutionDate() + 
				"', EnactmentDate = '" + ctpm.getEnactmentDate() + "', PushDateStart = '" + 
				ctpm.getPushDateStart() + "', PushDateEnd = '" + ctpm.getPushDateEnd() + "', MonthThemeName = '" + ctpm.getMonthThemeName() + 
				"', Constitutor = '" +  ctpm.getConstitutor() + "',TeacherID = " + ctpm.getTeacherID() + " where CMonthArrangeID = '" + ctpm.getCMonthArrangeID() + "'");
	}
	
	public void deleteCTPMonth(Statement statement, String cMonthArrangeID) throws SQLException {
		statement.execute("delete from collectivityteachingplanmonth where CMonthArrangeID='" + cMonthArrangeID + "'");
	}
	
	
	public CollectivityTeachingPlanMonth getCTPMonthByCMAID(String cMonthArrangeID) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		CollectivityTeachingPlanMonth ctpm = new CollectivityTeachingPlanMonth();
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from collectivityteachingplanmonth where CMonthArrangeID='" + cMonthArrangeID + "'");
		
		while (rs.next()) {
			int i = 1;
			ctpm.setCMonthArrangeID(rs.getString(i++));
			ctpm.setClassID(rs.getString(i++));
			ctpm.setExecutionDate(rs.getString(i++));
			ctpm.setEnactmentDate(rs.getString(i++));
			ctpm.setPushDateStart(rs.getString(i++));
			ctpm.setPushDateEnd(rs.getString(i++));
			ctpm.setMonthThemeName(rs.getString(i++));
			ctpm.setConstitutor(rs.getString(i++));
			ctpm.setTeacherID(rs.getInt(i++));
		}
		
		rs.close();
		stmt.close();
		conn.close();
		
		return ctpm;
	}
	
	public ArrayList<CollectivityTeachingPlanMonth> getAllCTeachingPlanMonth(int teacherID) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<CollectivityTeachingPlanMonth> al = new ArrayList<CollectivityTeachingPlanMonth>();
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();

		rs = stmt.executeQuery("select CMonthArrangeID,MonthThemeName,Constitutor, ClassID, PushDateStart, PushDateEnd from collectivityteachingplanmonth where teacherID = " + teacherID + " ORDER BY CMonthArrangeID ASC");
		CollectivityTeachingPlanMonth ctpm = null;
		
		while (rs.next()) {
			ctpm = new CollectivityTeachingPlanMonth();
			ctpm.setCMonthArrangeID(rs.getString("CMonthArrangeID"));
			ctpm.setMonthThemeName(rs.getString("MonthThemeName"));
			ctpm.setConstitutor(rs.getString("Constitutor"));
			ctpm.setClassID(rs.getString("ClassID"));
			ctpm.setPushDateStart(rs.getString("PushDateStart"));
			ctpm.setPushDateEnd(rs.getString("PushDateEnd"));
			
			al.add(ctpm);
		}
		rs.close();
		stmt.close();
		conn.close();
		
		return al;
	}
	
	public CollectivityTeachingPlanMonth getCTPMNetByCMAID(String cMonthArrangeID) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		CollectivityTeachingPlanMonth ctpm = new CollectivityTeachingPlanMonth();
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select ExecutionDate, MonthThemeName from collectivityteachingplanmonth where CMonthArrangeID='" + cMonthArrangeID + "'");
		
		while (rs.next()) {
			int i = 1;
			ctpm.setExecutionDate(rs.getString(i++));
			ctpm.setMonthThemeName(rs.getString(i++));
		}
		
		rs.close();
		stmt.close();
		conn.close();
		
		return ctpm;
	}
	
	public ArrayList<CollectivityTeachingPlanMonth> getSearchCondition(String monthThemeName,String executionDate, int teacherID) 
			throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<CollectivityTeachingPlanMonth> al = new ArrayList<CollectivityTeachingPlanMonth>();
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select CMonthArrangeID,MonthThemeName,Constitutor, ClassID, PushDateStart, PushDateEnd ");
		sqlStr.append("from collectivityteachingplanmonth ");
		sqlStr.append("where TeacherID=" + teacherID + " and CMonthArrangeID != '1' ");
		
		if(monthThemeName!=null && !monthThemeName.equals("")){
			sqlStr.append(" and MonthThemeName like '%" + monthThemeName + "%'");
		}
		if(executionDate!=null && !executionDate.equals("")){
			sqlStr.append(" and ExecutionDate = '" + executionDate + "'");
		}
		sqlStr.append(" ORDER BY CMonthArrangeID ASC ");
		
		rs = stmt.executeQuery(sqlStr.toString());
		
		CollectivityTeachingPlanMonth ctpm = null;
		
		while (rs.next()) {
			ctpm = new CollectivityTeachingPlanMonth();
			ctpm.setCMonthArrangeID(rs.getString("CMonthArrangeID"));
			ctpm.setMonthThemeName(rs.getString("MonthThemeName"));
			ctpm.setConstitutor(rs.getString("Constitutor"));
			ctpm.setClassID(rs.getString("ClassID"));
			ctpm.setPushDateStart(rs.getString("PushDateStart"));
			ctpm.setPushDateEnd(rs.getString("PushDateEnd"));
			
			al.add(ctpm);
		}
		rs.close();
		stmt.close();
		conn.close();
		
		return al;
	}
	
}
